from data_access.database import Session
from sqlalchemy.exc import SQLAlchemyError

from data_access.models.models import SQL


class SQLDAO:
    def __init__(self):
        self.session = Session()

    def add_sql(self, dispatch_code, sql_id, sql_type, sql_template, sql_variable, comment):
        """
        添加一个新的SQL记录到数据库。
        """
        new_sql = SQL(
            dispatch_code=dispatch_code,
            sql_id=sql_id,
            sql_type=sql_type,
            sql_template=sql_template,
            sql_variable=sql_variable,
            comment=comment
        )
        try:
            self.session.add(new_sql)
            self.session.commit()
            return True
        except SQLAlchemyError as e:
            self.session.rollback()
            print(f"Error occurred: {e}")
            return False
        finally:
            self.session.close()

    def get_sql_by_sql_id(self, dispatch_code, sql_id):
        """
        根据ID获取SQL记录。
        """
        try:
            return self.session.query(SQL).filter(SQL.dispatch_code == dispatch_code, SQL.sql_id == sql_id).first()
        except SQLAlchemyError as e:
            print(f"Error occurred: {e}")
            return None
        finally:
            self.session.close()

    def update_sql(self, sql_id, **kwargs):
        """
        更新SQL记录。
        """
        try:
            self.session.query(SQL).filter(SQL.sql_id == sql_id).update(kwargs)
            self.session.commit()
            return True
        except SQLAlchemyError as e:
            self.session.rollback()
            print(f"Error occurred: {e}")
            return False
        finally:
            self.session.close()

    def delete_sql(self, sql_id):
        """
        删除SQL记录。
        """
        try:
            self.session.query(SQL).filter(SQL.sql_id == sql_id).delete()
            self.session.commit()
            return True
        except SQLAlchemyError as e:
            self.session.rollback()
            print(f"Error occurred: {e}")
            return False
        finally:
            self.session.close()
